<?php

namespace app\engine\controller;

use cmf\controller\HomeBaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\Exception;
use think\exception\DbException;
use think\exception\PDOException;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Color;

//设置字体颜色
use PhpOffice\PhpSpreadsheet\Style\Alignment;

//设置对齐方式
use PhpOffice\PhpSpreadsheet\Style\Border;

//设置Border样式
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

//数字格式化
use PhpOffice\PhpSpreadsheet\Style\Fill;

//设置背景色

class IndexController extends HomeBaseController
{
    /**
     * 项目首页
     * @return mixed|string
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     * @throws DbException
     */
    public function index()
    {
        $data = Db::name('engine')->order('id desc')->select()->toArray();
        $role = Db::name('role_user')->where(['user_id' => cmf_get_current_admin_id()])->value('role_id');
        $adminId = cmf_get_current_admin_id();
        $this->assign('data', $data);
        $this->assign('role', $role);
        $this->assign('adminId', $adminId);
        return $this->fetch();
    }

    /**
     * 添加项目页
     * @return mixed|string
     */
    public function addEngine()
    {
        return $this->fetch();
    }

    /**
     * 添加项目
     */
    public function doadd()
    {
        $data = $this->request->param();
        if (empty($data['name'] || $data['Bidder'] || $data['unit'] || $data['control'] || $data['designer'] || $data['countPrice'] || $data['area'] || $data['floor'] || $data['contractDate'] || $data['address'])) {
            $this->error('缺少必要参数');
        }
        $check = Db::name('engine')->where(['name' => $data['name']])->find();
        if ($check) {
            $this->error('该项目名已被使用');
        }
        $res = Db::name('engine')->insert($data);
        if (!$res) {
            $this->error('添加失败');
        }
        $this->success('添加成功');
    }

    /**
     * 项目详情
     * @return mixed|string
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     * @throws DbException
     */
    public function see()
    {
        $data = $this->request->param();
        if (empty($data['id'])) {
            $this->error('缺少参数');
        }
        $res = Db::name('engine')->where(['id' => $data['id']])->find();
        $this->assign('res', $res);
        return $this->fetch();
    }

    /**
     * 删除项目页
     * @return mixed|string
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     * @throws DbException
     */
    public function del()
    {
        $data = Db::name('engine')->field('id,name')->select();
        $this->assign('data', $data);
        return $this->fetch();
    }

    /**
     * 删除项目
     * @throws Exception
     * @throws PDOException
     */
    public function dodel()
    {
        $data = $this->request->param();
        $res = Db::name('engine')->where(['id' => $data['name']])->delete();
        if (!$res) {
            $this->error('操作失败');
        }
        $this->success('操作成功');
    }

    /**
     * 项目编辑页
     * @return mixed|string
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     * @throws DbException
     */
    public function edit()
    {
        $role = Db::name('role_user')->where(['user_id' => cmf_get_current_admin_id()])->value('role_id');
        if ($role == 2 || $role == 4 || $role == 5) {
            $this->error('无权限操作');
        }
        $data = $this->request->param();
        $res = Db::name('engine')->where('id', $data['id'])->find();
        $this->assign('res', $res);
        return $this->fetch();
    }

    /**
     * 项目编辑
     * @throws Exception
     * @throws PDOException
     */
    public function doedit()
    {
        $data = $this->request->param();
        $id = $data['id'];
        unset($data['id']);
        $res = Db::name('engine')->where('id', $id)->update($data);
        if (!$res) {
            $this->error('操作失败');
        }
        $this->success('操作成功');
    }

    /**
     * 项目经理
     * @return mixed|string
     */
    public function manager()
    {
        $data = $this->request->param();
        $res = Db::name('engine')->where(['id' => $data['id']])->value('name');
        $this->assign('res', $res);
        $this->assign('data', $data);
        return $this->fetch();
    }

    /**
     * 财务
     * @return mixed|string
     */
    public function Finance()
    {
        $data = $this->request->param();
        $res = Db::name('engine')->where(['id' => $data['id']])->value('name');
        $this->assign('res', $res);
        $this->assign('data', $data);
        return $this->fetch();
    }

    public function output()
    {
        $role = Db::name('role_user')->where(['user_id' => cmf_get_current_admin_id()])->value('role_id');
        if ($role == 2 || $role == 4 || $role == 5) {
            $this->error('无权限操作');
        }
        //班组长
        $leader = Db::name('leader')->group('name')->field('name,type')->select()->toArray();
        //获取所有班组长的项目
        $arr = [];
        foreach ($leader as $k => $v) {
            $arr[$k]['name'] = $v['name'];
            $arr[$k]['type'] = $v['type'];
            $eid = Db::name('leader')->where($v)->group('eid')->field('eid')->select()->toArray();
            foreach ($eid as $j => $y) {
                $name = Db::name('engine')->where(['id' => $y['eid']])->field('id,name')->find();
                $arr[$k]['project'][$j] = $name;
            }
            $arr[$k]['project'] = array_values(array_filter($arr[$k]['project']));
        }
        //去除空项目
        foreach ($arr as $j => &$t) {
            if (!$t['project']) {
                unset($arr[$j]);
            }
        }
        //项目
        $program = array_values($arr);
        foreach ($program as $z => &$b) {
            //????
            $leaderId = Db::name('leader')->where(['name' => $b['name'], 'type' => $b['type']])->field('id')->select()->toArray();
            foreach ($b['project'] as $q => &$w) {
                //                    dump($q);
                $final = Db::name('final')->whereIn('lid', array_column($leaderId, 'id'))->where(['eid' => $w['id']])->find();
                if (is_null($final)) {
                    unset($program[$z]['project'][$z]);
                } else {
                    $w['deciPrice'] = $final['deciPrice'];
                    $w['paid'] = $final['paid'];
                }
                if (!array_key_exists('deciPrice', $w) || !array_key_exists('paid', $w)) {
                    unset($b['project'][$q]);
                }
            }
        }

        foreach ($program as $j => &$t) {
            if (!$t['project']) {
                unset($program[$j]);
            }
        }
        $program = array_values($program);
        foreach ($program as $k => $v) {
            $program[$k]['project'] = array_values($v['project']);
        }

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置列高度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(45);
        //需要设置行高度的行
        $rowStr = [1, 2, 3, 4, 5, 6];
        foreach ($rowStr as $k => $v) {
            $spreadsheet->getActiveSheet()->getRowDimension($v)->setRowHeight(60);
        }

        //设置A列字体大小/是否加粗
        $spreadsheet->getActiveSheet()->getStyle('A')->getFont()->setBold(true)->setSize(20);
        //设置A1:A9样式
        $Astyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ];
        $worksheet->getStyle('A1:A8')->applyFromArray($Astyle);


        $spreadsheet->getActiveSheet()->setCellValue('A1', '班组名称');
        $spreadsheet->getActiveSheet()->setCellValue('A2', '承办人');
        $spreadsheet->getActiveSheet()->setCellValue('A3', '项目名称');
        $spreadsheet->getActiveSheet()->setCellValue('A4', '总金额');
        $spreadsheet->getActiveSheet()->setCellValue('A5', '合同支付比例');
        $spreadsheet->getActiveSheet()->setCellValue('A6', '按合同支付金额');
        $spreadsheet->getActiveSheet()->setCellValue('A7', '核算总金额');
        $spreadsheet->getActiveSheet()->setCellValue('A8', '已支付金额');


        //设置起始列
        $cloum = 2;
        $row = 1;
        $cbRow = $row - 1;

        foreach ($program as $p => $o) {
            foreach ($o['project'] as $m => $n) {
                $Astyle = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER
                    ],
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'size' => 11
                    ],
                ];
                $worksheet->getStyle($this->loop($cloum) . ($row + 2))->applyFromArray($Astyle);
                $worksheet->getStyle($this->loop($cloum) . ($row + 3))->applyFromArray($Astyle);
                $worksheet->getStyle($this->loop($cloum) . ($row + 5))->applyFromArray($Astyle);
                $worksheet->getStyle($this->loop($cloum) . ($row + 2))->getAlignment()->setWrapText(true);
                $spreadsheet->getActiveSheet()->getColumnDimension($this->loop($cloum))->setAutoSize(true);
                $worksheet->setCellValue($this->loop($cloum) . ($row + 2), $n['name']);
                $worksheet->setCellValue($this->loop($cloum) . ($row + 3), $n['deciPrice']);
                $worksheet->setCellValue($this->loop($cloum) . ($row + 5), $n['paid']);
                $cloum++;
            }
        }

        //计算
        //sum(核算总金额)
        $Cstyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ],
            'font' => [
                'bold' => true,
                'size' => 14,
                'color' => [
                    'rgb' => '006400'
                ]
            ]
        ];
        $startCloum = 'B';
        $lastCloum = $this->loop($cloum);
        $Allcurrent = '4';
        $str = '=SUM(' . $startCloum . $Allcurrent . ':' . $lastCloum . $Allcurrent . ')';
        $worksheet->getStyle('B7')->applyFromArray($Cstyle);
        $worksheet->setCellValue('B7', $str);
        $Allcurrent = '6';
        $pay = '=SUM(' . $startCloum . $Allcurrent . ':' . $lastCloum . $Allcurrent . ')';
        $worksheet->getStyle('B8')->applyFromArray($Cstyle);
        $worksheet->setCellValue('B8', $pay);
        //设置样式


        $cloum = 2;
        $row = 1;
        $cbRow = $row - 1;
        foreach ($program as $k => $v) {
            $countCloum = count($v['project']);
            $spreadsheet->getActiveSheet()->mergeCells($this->loop($cloum) . $row . ':' . $this->loop($cloum + $countCloum - 1) . $row);
            $spreadsheet->getActiveSheet()->mergeCells($this->loop($cloum) . ($row + 1) . ':' . $this->loop($cloum + $countCloum - 1) . ($row + 1));

            $Astyle = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'vertical' => Alignment::VERTICAL_CENTER
                ],
                'font' => [
                    'name' => 'Arial',
                    'bold' => true,
                    'size' => 14
                ],
            ];
            $worksheet->getStyle($this->loop($cloum) . $row)->applyFromArray($Astyle);
            $worksheet->getStyle($this->loop($cloum) . ($row + 1))->applyFromArray($Astyle);

            $worksheet->setCellValue($this->loop($cloum) . $row, $v['type']);
            $worksheet->setCellValue($this->loop($cloum) . ($row + 1), $v['name']);

            $cloum += $countCloum;
        }


        $spreadsheet->getProperties()
            ->setCreator("心月")    //作者
            ->setLastModifiedBy("心月") //最后修改者
            ->setTitle("test sheet Excel")  //标题
            ->setSubject("test Excel") //副标题
            ->setDescription("test excel description")  //描述
            ->setKeywords("excel") //关键字
            ->setCategory("test"); //分类

        //输出保存
        //文件名
        $fileName = date('YmdHis', time()) . '.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Cache-Control: max-age=0');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        //输出
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    function getRandChar($length)
    {
        $str = null;
        $strPol = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyz0123456789";
        $max = strlen($strPol) - 1;

        for ($i = 0; $i < $length; $i++) {
            $str .= $strPol[rand(0, $max)];
        }

        return $str;
    }

    /**
     * 生成excel列序号.
     */
    public function loop($n = 1)
    {
        $loop = $n - 1 ?? 1;
        $charnum = 65;
        for (; $loop < 150; $loop++) {

            $quotient = intval($loop / 26);
            $remainder = $loop % 26;
            $f = $quotient > 0 ? chr($charnum + $quotient - 1) : '';

            $s = $remainder >= 0 ? chr($charnum + $remainder) : '';
            return $f . $s;
        }
    }
}
